/**
 *
 * MySQL操作封装类
 *
 * 对mysql2模块进行数据库操作二次封装
 *
 */

const MySQL = require('mysql2')

class mysql {

	/**
	 *
	 * 构造函数
	 *
	 * @param [class] common 工具库
	 * @param [object] config 配置数据
	 *
	 */

	constructor(common, config, crypto, dataKey, dataIv) {
		this.common = common
		this.config = config
		this.crypto = crypto
		this.dataKey = dataKey
		this.dataIv = dataIv
		let {host, port, user, pwd, name, charset} = this.config
		this.db = MySQL.createPool({
			host,
			port,
			user,
			password: pwd,
			database: name,
			charset,
			connectionLimit: 30
		})
		this.db.on('connection', () => {
			console.log(`mysql db ${this.db.name} is connected`)
		})
		this.db.on('release', () => {
			console.log(`mysql db ${this.db.name} is release`)
		})
		this.db.name = name
	}

	/**
	 *
	 * 错误处理器
	 *
	 * @param [string] name 连接的数据库名称
	 * @param [object] err 数据库操作错误内容
	 *
	 */

	errHandler(name, err) {
		let msg = ''
		switch(err.code) {
			case 'PROTOCOL_CONNECTION_LOST':
			case 'ECONNRESET':
			case 'ECONNREFUSED':
			case 'EPIPE':
				msg = `${name} connect failed`
			break
			case 'ER_BAD_DB_ERROR':
				msg = err.sqlMessage
			break
			case 'ER_DATA_TOO_LONG':
				msg = 'insert data too long'
			break
			default:
				msg = `${name} error`
		}
		const errMsg = `mysql db ${name} error => errcode: ${err.code}, errno: ${err.errno}, sqlState: ${err.sqlState}, sqlMsg: ${err.sqlMessage}, otherMsg: ${msg}`
		console.error(errMsg)
		this.common.alertMsg('MySQL连接挂啦', errMsg)
		this.common.writeLog('error', errMsg)
	}

	resolve(data = 0) {
		return new Promise((resolve) => {
			resolve(data)
		})
	}

	reject(code, msg) {
		return new Promise((resolve, reject) => {
			reject({
				code,
				msg
			})
		})
	}



	/**
	 *
	 * 检查连接状态并返回连接实例
	 *
	 * @param [string] name 连接的数据库名称
	 *
	 * @return [string] name 连接的数据库名称
	 *
	 */

	getConn() {
		return new Promise((resolve, reject) => {
			this.db.getConnection((err, connection) => {
				if(err) {
					this.errHandler(this.db.name, err)
					reject({
						code: '-1005',
						msg: 'database connection failed'
					})
					return
				}
				connection.name = this.db.name
				//采用键值对格式查询
				connection.config.queryFormat = function (query, values) {
					if (!values)
						return query
					let index = 0
					return query.replace(/\$(\w+)/g, function (txt, key) {
						if (values.hasOwnProperty(key)) {
							return this.escape(values[key])
						}
						else if(values.hasOwnProperty(`${key}${index}`)) {
							return this.escape(values[`${key}${index++}`])
						}
						return txt
					}.bind(this))
				}
				Object.assign(connection, {
					common: this.common,
					encryptTableFields: this.config.encryptTableFields,
					dataKey: this.dataKey,
					dataIv: this.dataIv,
					_query: this.query,
					execute: this.execute,
					insert: this.insert,
					delete: this.delete,
					select: this.select,
					update: this.update,
					insertPrepare: this.insertPrepare,
					deletePrepare: this.deletePrepare,
					selectPrepare: this.selectPrepare,
					updatePrepare: this.updatePrepare,
					errHandler: this.errHandler,
					resolve: this.resolve,
					reject: this.reject,
					encryptData: this.encryptData,
					decryptData: this.decryptData,
					crypto: this.crypto,
					trans: this.trans,
					cmt: this.commit,
					createTable: this.createTable,
					tableExists: this.tableExists
				})
				resolve(connection)
			})
		})
	}

	/**
	 *
	 * 关闭数据库连接
	 *
	 */

	async close() {
		return new Promise((resolve, reject) => {
			this.db.end((err) => {
				if(err) {
					console.error(`mysql pool ${name} close failed`, err)
					reject({
						code: '-1071',
						msg: 'close resource failed'
					})
				}
				else
					resolve()
			})
		})
	}

	async trans(mustErr) {
		return new Promise( async (resolve, reject) => {
			try {
				this.beginTransaction((err) => {
					if(err) {
						console.error(`database transaction create failed:`, err)
						reject(mustErr || {
							code: '-1037',
							msg: 'database transaction create failed'
						})
					}
					else {
						console.log('database transaction create success')
						this.transed = true
						resolve()
					}
				})
			}
			catch(err) {
				console.error(`database transaction create failed:`, err)
				reject(mustErr || {
					code: '-1037',
					msg: 'database transaction create failed'
				})
			}
		})
	}

	async commit(mustErr) {
		return new Promise( async (resolve, reject) => {
			try {
				this.commit((err) => {
					if(err) {
						console.error(`database transaction commit error:`, err)
						this.rollback((_err) => {
							if(_err) {
								console.error(`database transaction rollback error:`, _err)
							}
							console.log('database transaction rollback ok')
						})
						reject(mustErr || {
							code: '-1046',
							msg: 'database transaction commit failed'
						})
						return
					}
					console.log('database transaction commit success')
					this.commited = true
					resolve()
				})
			}
			catch(err) {
				console.error(`database transaction commit error:`, err)
				reject(mustErr || {
					code: '-1046',
					msg: 'database transaction commit failed'
				})
			}
		})	
	}

	encryptData(data) {
		if(Object.prototype.toString.call(data) === '[object Object]' || Object.prototype.toString.call(data) === '[object Array]')
			data = JSON.stringify(data)
		return this.crypto.encryptAES(data, this.dataKey, this.dataIv)
	}

	decryptData(data) {
		data = this.crypto.decryptAES(data, this.dataKey, this.dataIv)
		const temp = this.common.isJson(data)
		return temp ? temp : data
	}

	/**
	 *
	 * 执行语句操作
	 *
	 * @param [string] sql SQL语句
	 * @param [array] data 参数数组
	 *
	 * @method db.query('select * from table')
	 * @method db.query('select * from table where column = ?', [1])
	 *
	 * @return [array] result 查询的结果数组
	 *
	 */

	async query(sql, data = {}, err) {
		return new Promise( async (resolve, reject) => {
			try {
				console.log(sql, data)
				this.query(sql, data, (err, result) => {
					if(err) {
						console.error('database execute sql failed', sql)
						if(this.transed) {
							this.rollback((_err) => {
								if(_err) {
									console.error(`database transaction rollback error:`, _err)
								}
								console.log('database transaction rollback ok')
							})
						}
						this.errHandler(this.name, err)
						reject(err || {
							code: '-1006',
							msg: 'database execute query failed'
						})
					}
					else {
						resolve(result)
					}
					this.release()
				})
			}
			catch(err) {
				console.error('database execute sql failed', err.sql)
				this.errHandler(this.name, err)
				reject({
					code: '-1006',
					msg: 'database query failed'
				})
			}
		})
	}

	async createTable({table, field = [], attr = {}}) {
		if(field.length == 0)
			return this.reject('-1065', 'data table must be have fields')
		let fieldsStr  = ''
		for(let f of field) {
			fieldsStr += `${f},`
		}
		fieldsStr = this.common.rtrim(fieldsStr, ',')
		await this._query("CREATE TABLE IF NOT EXISTS `" + table + "` (" + fieldsStr + ") ENGINE=InnoDB" + (attr.autoId ? ' AUTO_INCREMENT=1 ' : ' ') + "DEFAULT CHARSET=" + (attr.charset || 'utf8mb4') + " COLLATE=" + (attr.collate || 'utf8mb4_bin') + " ROW_FORMAT=" + (attr.rowFormat || 'COMPACT') + " COMMENT='" + (attr.comment || '') + "'")
	}

	async tableExists({table}) {
		const result = await this._query(`show tables like '${table}'`)
		if(result[0])
			return true
		else
			return false
	}

	async execute({sql, data: sourceData, err, encrypt = [], decrypt = []}) {
		sourceData = sourceData || {}
		try {
			if(encrypt)
				encrypt = encrypt instanceof Array ? encrypt : [encrypt]
			let data = JSON.parse(JSON.stringify(sourceData))
			for(let f of encrypt)
				if(data[f])
					data[f] = this.encryptData(data[f])
			let result = await this._query(sql, data, err || {
				code: '-1007',
				msg: 'database execute query failed'
			})
			if(decrypt && result instanceof Array) {
				decrypt = decrypt instanceof Array ? decrypt : [decrypt]
				for(let index in result) {
					for(let f of decrypt)
						if(result[index][f])
							result[index][f] = this.decryptData(result[index][f])
				}
			}
			return this.resolve(result)
 		}
 		catch(_err) {
 			console.error(`database execute query failed ${sql}`, _err)
			return this.reject('-1007', 'database execute query failed')
 		}
	}

	/**
	 *
	 * 插入操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] fields 插入的字段名，用,分割
	 * @param [array] data 参数数组
	 *
	 * @method db.insert('tableName', 'insertColumn1, insertColumn2, ...', ['001', 'test', ...])
	 * @method db.insert('tableName', 'insertColumn1, insertColumn2, ...', [['001', 'test', ...], ['002', 'test1', ...]])
	 *
	 * @return [number] 成功插入的条数
	 *
	 */

	async insert(params) {
 		try {
 			const {sql, data} = await this.insertPrepare(params)
			const result = await this._query(sql, data, {
				code: '-1007',
				msg: 'database execute query failed'
			})
			return this.resolve(result.affectedRows)
 		}
 		catch(err) {
 			console.error('database execute insert failed', err)
			return this.reject('-1007', 'database execute query failed')
 		}
	}

	async insertPrepare({table, field, data: sourceData, encrypt = []}) {
		sourceData = sourceData || {}
		if(!table)
			return this.reject('-1047', 'database create query params invalid')
		table = table instanceof Array ? table : [table]
		if(encrypt)
			encrypt = encrypt instanceof Array ? encrypt : [encrypt]
		let data = JSON.parse(JSON.stringify(sourceData))
		for(let t of table) {
			const _t = t.match(/((\w+) |(\w+))/)[1].trim()
			if(this.encryptTableFields[this.name] && this.encryptTableFields[this.name][_t])
				encrypt = encrypt.concat(this.encryptTableFields[this.name][_t])
		}
		let groups = []
		if(data instanceof Array) {
			let _data = {}
			let index = 0
			for(let data1 of data) {
				for(let key in data1) {
					let temp
					if(encrypt.indexOf(key) != -1)
						temp = this.encryptData(data1[key])
					else
						temp = data1[key]
					_data[`${key}${index++}`] = temp
				}
				groups.push(Object.keys(data1))
			}
			data = _data;
		}
		else {
			for(let f of encrypt)
				if(data[f])
					data[f] = this.encryptData(data[f])
			groups.push(Object.keys(data))
		}
		let groupVal = '';
		let vals = '';
		for(let group of groups) {
			for(let key of group) {
				vals += `,$${key}`
			}
			vals = vals.substr(1, vals.length)
			groupVal += `,(${vals})`
			vals = ''
		}
		groupVal = groupVal.substr(1, groupVal.length)
		const assemblyes = [
			'insert into',
			table.join(','),
			'(',
			field ? (field instanceof Array ? field.join(',') : field) : '*',
			') values',
			groupVal
		]
		let sql = ''
		assemblyes.forEach(a => sql += `${a} `)
		return {
			sql: sql.trim(),
			data
		}
	}

	/**
	 *
	 * 删除操作
	 *
	 * @param [string] table 数据表名
	 * @param [string] where 条件语句字符串
	 * @param [array] data 参数数组
	 *
	 * @mehtod db.delete('tableName', 'tableColumn1 = ? or tableColumn12= ? or ...', ['001', '002', ...])
	 *
	 * @return [number] 成功删除的条数
	 *
	 */

	async delete(params) {
		const {sql, data} = await this.deletePrepare(params)
		let result = await this._query(sql, data, {
			code: '-1008',
			msg: 'database execute query failed'
		})
		return this.resolve(result.affectedRows)
	}

	async deletePrepare({table, where, data: sourceData, ignoreWhere = false, encrypt = []}) {
		sourceData = sourceData || {}
		if(!table || (!where && !ignoreWhere))
			return this.reject('-1047', 'database create params invalid')
		table = table instanceof Array ? table : [table]
		let data = JSON.parse(JSON.stringify(sourceData))
		if(encrypt)
			encrypt = encrypt instanceof Array ? encrypt : [encrypt]
		for(let t of table) {
			const _t = t.match(/((\w+) |(\w+))/)[1].trim()
			if(this.encryptTableFields[this.name] && this.encryptTableFields[this.name][_t])
				encrypt = encrypt.concat(this.encryptTableFields[this.name][_t])
		}
		for(let f of encrypt)
			if(data[f])
				data[f] = this.encryptData(data[f])
		const assemblyes = [
			'delete',
			'from',
			table.join(','),
			where ? (`where ${where.replace(/\&\&/g, 'and').replace(/\|\|/g, 'or')}`) : ''
		]
		let sql = ''
		assemblyes.forEach(a => sql += `${a} `)
		return {
			sql: sql.trim(),
			data
		}
	}

	/**
	 *
	 * 查询操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] fields 查询的字段名，用,分割
	 * @param [string] where 条件字符串
	 * @param [array] data 参数数组
	 * @param [string] sort 排序字符串
	 * @param [string] limit 截取条数字符串
	 *
	 * @method db.select('tableName')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...', 'tableColumn1 = 'test', tableColumn2 = 1, ...')
	 * @method db.select('tableName', 'tableColumn1, tableColumn2, ...', 'tableColumn1 = ?, tableColumn2 = ?, ...', ['value1', 'value2', ...])
	 *
	 * @return [array] 查询的结果数组
	 *
	 */

	async select(params) {
		try {
			let {sql, data, decrypt} = await this.selectPrepare(params);
			let result = await this._query(sql, data, {
				code: '-1009',
				msg: 'database execute query failed'
			})
			if(decrypt) {
				for(let index in result) {
					for(let f of decrypt)
						if(result[index][f]) {
							result[index][f] = this.decryptData(result[index][f])
						}
				}
			}
			return this.resolve(result)
		}
		catch(err) {
			console.error('database execute select failed', err)
			return this.reject('-1009', 'database execute query failed')
		}
	}

	async selectPrepare({table, field, where, group, sort, limit, distinct, data: sourceData, encrypt = [], decrypt = []}) {
		sourceData = sourceData || {}
		if(!table)
			return this.reject('-1047', 'database create params invalid')
		table = table instanceof Array ? table : [table]
		let data = JSON.parse(JSON.stringify(sourceData))
		if(encrypt)
			encrypt = encrypt instanceof Array ? encrypt : [encrypt]
		for(let t of table) {
			const _t = t.match(/((\w+) |(\w+))/)[1].trim()
			if(this.encryptTableFields[this.name] && this.encryptTableFields[this.name][_t])
				encrypt = encrypt.concat(this.encryptTableFields[this.name][_t])
		}
		for(let f of encrypt)
			if(data[f])
				data[f] = this.encryptData(data[f])
		const assemblyes = [
			'select',
			distinct ? 'distinct' : '',
			field ? (field instanceof Array ? field.join(',') : field) : '*',
			'from',
			table.join(','),
			where ? (`where ${where.replace(/\&\&/g, 'and').replace(/\|\|/g, 'or')}`) : '',
			group ? `group by ${group instanceof Array ? group.join(',') : group}` : '',
			sort && sort.field && sort.order ? `order by ${sort.field instanceof Array ? sort.field.join(',') : sort.field} ${sort.order}` : '',
			!isNaN(limit) ? `limit ${limit}` : (limit && !isNaN(limit.start) && !isNaN(limit.count) ? `limit ${limit.start},${limit.count}` : '')
		]
		let sql = ''
		assemblyes.forEach(a => sql += (a ? `${a} ` : ''))
		return {
			sql: sql.trim(),
			data,
			decrypt: encrypt.concat(decrypt)
		}
	}

	/**
	 *
	 * 修改操作
	 *
	 * @param [string] table 数据表名称
	 * @param [string] updateFields 更新的字段名，用,分开
	 * @param [string] where 条件字符串
	 * @param [array] data 参数数组
	 *
	 * @method db.update('tableName', 'tableUpdateColumn1=123', 'tableColumn1=1')
	 * @method db.update('tableName', 'tableUpdateColumn1=？, ...', 'tableColumn1=?, ...', [10, 1, ...])
	 *
	 * @return [number] 成功修改的条数
	 *
	 */

	 async update(params) {
		try {
			const {sql, data} = await this.updatePrepare(params)
			const result = await this._query(sql, data, {
				code: '-1010',
				msg: 'database execute query failed'
			})
			return this.resolve(result.affectedRows)
		}
		catch(err) {
			console.error('database execute update failed', err)
			return this.reject('-1010', 'database execute query failed')
		}
	}

	async updatePrepare({table, field, where, data: sourceData, ignoreWhere = false, encrypt = []}) {
		sourceData = sourceData || {}
		if(!table || !field || (!where && !ignoreWhere))
			return this.reject('-1047', 'database create params invalid')
		table = table instanceof Array ? table : [table]
		let data = JSON.parse(JSON.stringify(sourceData))
		if(encrypt)
			encrypt = encrypt instanceof Array ? encrypt : [encrypt]
		for(let t of table) {
			const _t = t.match(/((\w+) |(\w+))/)[1].trim()
			if(this.encryptTableFields[this.name] && this.encryptTableFields[this.name][_t])
				encrypt = encrypt.concat(this.encryptTableFields[this.name][_t])
		}
		for(let f of encrypt)
			if(data[f])
				data[f] = this.encryptData(data[f])
		const assemblyes = [
			'update',
			table.join(','),
			'set',
			field ? (field instanceof Array ? field.join(',') : field) : '',
			where ? (`where ${where.replace(/\&\&/g, 'and').replace(/\|\|/g, 'or')}`) : ''
		]
		let sql = ''
		assemblyes.forEach(a => sql += `${a} `)
		return {
			sql: sql.trim(),
			data
		}
	}

}

module.exports = mysql